﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Threading;
using System.Data;
using System.ComponentModel;
using Microsoft.Win32;
using System.Data.OleDb;

namespace EnerForecast
{
    /// <summary>
    /// Interaction logic for LoadDataWindow.xaml
    /// </summary>
    public partial class LoadDataWindow : Window
    {

        DataSet dsTempDay;
        BackgroundWorker dbTempDayinserter = new BackgroundWorker();
        BackgroundWorker dbCostHourinserter = new BackgroundWorker();
        int errornum = 0, duplinum = 0;
        DataSet ds600RT, ds1100RT,dsCondition,dsIcing;
        List<string> sheets;

        public LoadDataWindow()
        {
            InitializeComponent();
        }
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel|*.xls|Excel|*.xlsx|所有文件|*.*";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;

            if (openFileDialog.ShowDialog().GetValueOrDefault())
            {
                //得到excel中的所有sheet名字，然后循环得到数据模拟表达式
                string workerFileName = openFileDialog.FileName;
                //List<string> sheets = GetSheetNames(workerFileName);

                //int sheetsCount = sheets.Count();
                ////int iStep = 0;
                //progressbar.Maximum = sheetsCount;
                //progressbar.Value = 0;
                //FFtest.Class1 test = new FFtest.Class1();
                //foreach (string sheet in sheets)
                //{
                //    MathWorks.MATLAB.NET.Arrays.MWArray mArray = test.myFuctionFitting(workerFileName, sheet);
                //    MathWorks.MATLAB.NET.Arrays.MWNumericArray mmArray = mArray as MathWorks.MATLAB.NET.Arrays.MWNumericArray;
                //    Array array = mmArray.ToArray();
                //}
                dsTempDay = GetSheetbySheetName(workerFileName);
                //datagrid.ItemsSource = dsTempDay.Tables[0].DefaultView;
                progressbar.Maximum = dsTempDay.Tables[0].Rows.Count;
                progressbar.Value = 0;

                dbTempDayinserter.WorkerReportsProgress = true;
                dbTempDayinserter.WorkerSupportsCancellation = true;

                //dbTempDayinserter.DoWork += new DoWorkEventHandler(dbTempDay_Doinsert);//冷
                dbTempDayinserter.DoWork += new DoWorkEventHandler(dbTempDay_Doinsert_heat);//热
                dbTempDayinserter.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler(dbTempDayinserter_ProgressChanged);
                dbTempDayinserter.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(dbTempDayinserter_Completed);
                dbTempDayinserter.RunWorkerAsync();
                lb_status.Content = "开始处理..";
                btn_insert.IsEnabled = false;

            }
        }

        //从excel表中得到每个sheet的名字
        public static List<string> GetSheetNames(string fileName)
        {
            List<string> retSheetNames = new List<string>();
            string strHead = "NO";
            string excelFilepath = fileName;
            string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilepath + ";Extended Properties=\"Excel 12.0;HDR=" + strHead + ";\"";
            if ((System.IO.Path.GetExtension(excelFilepath)).ToLower() == ".xls")
            {
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilepath + ";Extended Properties=\"Excel 8.0;HDR=" + strHead + ";IMEX=1\"";
            }

            using (OleDbConnection conn = new OleDbConnection(strCon))
            {
                //打开oledb连接
                conn.Open();

                //返回在目录中定义的工作表
                System.Data.DataTable schemeTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                for (int i = 0; i < schemeTable.Rows.Count; i++)
                {
                    //工作表名 schemeTable.Rows[i][2].ToString().Trim()
                    string temp = (schemeTable.Rows[i][2].ToString().Trim());
                    //int tmp = GetNumber(temp);
                    //retSheetNames.Add(tmp.ToString());
                    temp = temp.Substring(1, temp.Length - 3);
                    retSheetNames.Add(temp);
                }
                return retSheetNames;
            }
        }

        //从Excel表中得到表一数据
        public DataSet GetSheetbySheetName(string fileName, string sheetName = "Sheet1")
        {
            DataSet ds = new DataSet();
            string excelpath = fileName;
            //确定excel版本
            string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelpath + ";Extended Properties=\"Excel 12.0;\"";
            if ((System.IO.Path.GetExtension(excelpath)).ToLower() == ".xls")
            {
                strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelpath + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
            }
            using (OleDbConnection conn = new OleDbConnection(strconn))
            {
                //打开oledb连接
                conn.Open();
                //取sheet1的数据
                string strCom = "SELECT * FROM [" + sheetName + "$]";
                OleDbDataAdapter myadapter = new OleDbDataAdapter(strCom, conn);
                myadapter.Fill(ds);
            }

            return ds;
        }

        //确认为月份
        private int ismonth(string month)
        {
            switch (month)
            {
                case "1":
                    return 1;
                case "2":
                    return 2;
                case "3":
                    return 3;
                case "4":
                    return 4;
                case "5":
                    return 5;
                case "6":
                    return 6;
                case "7":
                    return 7;
                case "8":
                    return 8;
                case "9":
                    return 9;
                case "10":
                    return 10;
                case "11":
                    return 11;
                case "12":
                    return 12;
                default:
                    return 0;
            }
        }

        //完成温度日能耗数据导入
        private void dbTempDayinserter_Completed(object sender, RunWorkerCompletedEventArgs e)
        {
            btn_insert.IsEnabled = true;
            if (errornum == 0)
            {
                lb_status.Content = "完成..";
                lb_fittingstatus.Content = "后台开始拟合温度日能耗关系函数";
                Thread thtempcostfitting = new Thread(new ThreadStart(tempcostfitting));
                thtempcostfitting.Start();
                //ForeCastDataSetTableAdapters.tb_temp_costPdayTableAdapter datahelper = new ForeCastDataSetTableAdapters.tb_temp_costPdayTableAdapter();
                //ForeCastDataSet.tb_temp_costPdayDataTable ss = new ForeCastDataSet.tb_temp_costPdayDataTable();

                //datahelper.Fill(ss);
                //datagrid.ItemsSource = ss;
                //
            }
            else
            {
                MessageBox.Show("数据导入出错");
            }
        }
        public void tempcostfitting()
        {
            FunctionFitter FF = new FunctionFitter();
            try
            {
                FF.TempCostFit();
                this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content = "温度能耗关系函数拟合完成"));
            }
            catch (Exception e)
            {
                MessageBox.Show("matlab动态库链接异常，请检查软件运行环境");
            }
        }

        private void dbTempDayinserter_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressbar.Value = e.ProgressPercentage;
            lb_status.Content = "处理中，请勿关闭.." + progressbar.Value + "/" + progressbar.Maximum + ".." + errornum + "个出错项.." + duplinum + "个重复项";
        }

        private void dbTempDay_Doinsert(object sender, DoWorkEventArgs e)    //冷
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            errornum = 0;
            duplinum = 0;
            int succeednum = 0;
            ForeCastDataSetTableAdapters.tb_temp_costPdayTableAdapter datahelper = new ForeCastDataSetTableAdapters.tb_temp_costPdayTableAdapter();
            ForeCastDataSet.tb_temp_costPdayDataTable temptable = new ForeCastDataSet.tb_temp_costPdayDataTable();
            Dry2Wet Tempconvert = new Dry2Wet();
            foreach (DataRow i in dsTempDay.Tables[0].Rows)
            {
                try
                {
                    succeednum++;
                    bw.ReportProgress(succeednum);
                    temptable = datahelper.GetDataByDay(i[3].ToString());
                    double Hightemp = double.Parse(i[0].ToString());
                    double Lowtemp = double.Parse(i[1].ToString());
                    double Wet = double.Parse(i[4].ToString());
                    double DryTemp = Math.Floor((Hightemp + Lowtemp) / 2);
                    double WetTemp = Tempconvert.Convert2wet(int.Parse(DryTemp.ToString()), Wet);
                    ////转换为湿球温度,存于数据库原本的低温字段
                    if (temptable.Rows.Count != 0)
                    {
                        datahelper.UpdateQueryWithoutDay(Hightemp, WetTemp, double.Parse(i[2].ToString()), i[3].ToString());
                        duplinum++;
                    }
                    else
                    {
                        datahelper.InsertQuery(Hightemp, WetTemp, double.Parse(i[2].ToString()), i[3].ToString());
                    }
                }
                catch (Exception)
                {
                    errornum++;
                }
            }

        }    
        private void dbTempDay_Doinsert_heat(object sender, DoWorkEventArgs e)   // 热
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            errornum = 0;
            duplinum = 0;
            int succeednum = 0;
            ForeCastDataSetTableAdapters.tb_temp_costPday1TableAdapter datahelper = new ForeCastDataSetTableAdapters.tb_temp_costPday1TableAdapter();
            ForeCastDataSet.tb_temp_costPday1DataTable temptable = new ForeCastDataSet.tb_temp_costPday1DataTable();
            Dry2Wet Tempconvert = new Dry2Wet();
            foreach (DataRow i in dsTempDay.Tables[0].Rows)
            {
                try
                {
                    succeednum++;
                    bw.ReportProgress(succeednum);
                    temptable = datahelper.GetDataByDay(i[3].ToString());
                    double Hightemp = double.Parse(i[0].ToString());
                    double Lowtemp = double.Parse(i[1].ToString());
                    double Wet = double.Parse(i[4].ToString());
                    double DryTemp = Math.Floor((Hightemp + Lowtemp) / 2);
                    double WetTemp = Tempconvert.Convert2wet(int.Parse(DryTemp.ToString()), Wet);
                    ////转换为湿球温度,存于数据库原本的低温字段
                    if (temptable.Rows.Count != 0)
                    {
                        datahelper.UpdateQueryWithoutDay(Hightemp, WetTemp, double.Parse(i[2].ToString()), i[3].ToString());
                        duplinum++;
                    }
                    else
                    {
                        datahelper.InsertQuery(Hightemp, WetTemp, double.Parse(i[2].ToString()), i[3].ToString());
                    }
                }
                catch (Exception)
                {
                    errornum++;
                }
            }

        }
        //插入每月逐时信息
        private void btn_insert_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel|*.xls|Excel|*.xlsx|所有文件|*.*";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;

            if (openFileDialog.ShowDialog().GetValueOrDefault())
            {
                //得到excel中的所有sheet名字，然后循环得到数据模拟表达式
                string workerFileName = openFileDialog.FileName;
                sheets = GetSheetNames(workerFileName);


                dbCostHourinserter.WorkerReportsProgress = true;
                dbCostHourinserter.WorkerSupportsCancellation = true;

                dbCostHourinserter.DoWork += new DoWorkEventHandler(dbCostHour_Doinsert);
                dbCostHourinserter.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler(dbCostHourinserter_ProgressChanged);
                dbCostHourinserter.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(dbCostHourinserter_Completed);
                dbCostHourinserter.RunWorkerAsync(workerFileName);
                btn_temp_cost.IsEnabled = false;
                lb_status.Content = "开始处理..";

            }
        }
        private void dbCostHourinserter_Completed(object sender, AsyncCompletedEventArgs e)
        {
            progressbar.Value = progressbar.Maximum;
            lb_status.Content = "所有表数据导入完毕";
            btn_temp_cost.IsEnabled = true;
            //新建线程用作每月日逐时温度函数拟合

            //
            //
        }

        private void dbCostHourinserter_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressbar.Value = e.ProgressPercentage;
            int sheetno = (int)e.UserState;
            lb_status.Content = "处理中，请勿关闭.." + progressbar.Value + "/" + progressbar.Maximum + "..正在填写表： " + sheets[sheetno] + "..还有" + (sheets.Count - sheetno - 1).ToString() + "张数据表待导入" + ".." + errornum + "个出错项.." + duplinum + "个重复项";

        }

        private void dbCostHour_Doinsert(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            string filename = e.Argument.ToString();
            for (int i = 0; i < sheets.Count; i++)
            {
                //确定表单名称为月份，否则非法不插入
                if (ismonth(sheets[i]) != 0)
                {
                    DataSet dsCostHour;
                    dsCostHour = GetSheetbySheetName(filename, sheets[i]);
                    int imonth = int.Parse(sheets[i]);

                    this.Dispatcher.BeginInvoke(new Action(() => progressbar.Maximum = dsCostHour.Tables[0].Rows.Count));
                    this.Dispatcher.BeginInvoke(new Action(() => progressbar.Value = 0));
                    //数据导入信息设置
                    errornum = 0;
                    duplinum = 0;
                    int succeednum = 0;
                    ForeCastDataSet.tb_costPhour1DataTable temptable = new ForeCastDataSet.tb_costPhour1DataTable();
                    ForeCastDataSetTableAdapters.tb_costPhour1TableAdapter datahelper = new ForeCastDataSetTableAdapters.tb_costPhour1TableAdapter();
                    //上午
                    DataRow[] drCostHourAM = dsCostHour.Tables[0].Select("Time like '" + sheets[i] + "%' and Time like '%AM%'");
                    foreach (DataRow row in drCostHourAM)
                    {
                        try
                        {
                            succeednum++;
                            bw.ReportProgress(succeednum, i);
                            //截取月信息和时间信息
                            int clockstart = row["Time"].ToString().IndexOf(' ');
                            int clockend = row["Time"].ToString().IndexOf(':');
                            int iclock;
                            string clock = row["Time"].ToString().Substring(clockstart + 1, clockend - clockstart - 1);
                            if (clock == "12")
                            {
                                iclock = 24;
                            }
                            else
                            {
                                iclock = int.Parse(clock);
                            }
                            temptable = datahelper.GetDataByTime(row["Time"].ToString());
                            if (temptable.Rows.Count != 0)
                            {
                                datahelper.UpdateQueryWithoutTime(double.Parse(row[1].ToString()), iclock, imonth, row["Time"].ToString());
                                duplinum++;
                            }
                            else
                            {
                                datahelper.InsertQuery(row["Time"].ToString(), double.Parse(row[1].ToString()), iclock, imonth);
                            }
                        }
                        catch (Exception)
                        {
                            errornum++;
                        }
                    }
                    //下午
                    DataRow[] drCostHourPM = dsCostHour.Tables[0].Select("Time like '" + sheets[i] + "%' and Time like '%PM%'");
                    foreach (DataRow row in drCostHourPM)
                    {
                        try
                        {
                            succeednum++;
                            bw.ReportProgress(succeednum, i);
                            //截取月信息和时间信息
                            int clockstart = row["Time"].ToString().IndexOf(' ');
                            int clockend = row["Time"].ToString().IndexOf(':');
                            int iclock;
                            string clock = row["Time"].ToString().Substring(clockstart + 1, clockend - clockstart - 1);
                            if (clock == "12")
                            {
                                iclock = 12;
                            }
                            else
                            {
                                iclock = int.Parse(clock) + 12;
                            }
                            temptable = datahelper.GetDataByTime(row["Time"].ToString());
                            if (temptable.Rows.Count != 0)
                            {
                                datahelper.UpdateQueryWithoutTime(double.Parse(row[1].ToString()), iclock, imonth, row["Time"].ToString());
                                duplinum++;
                            }
                            else
                            {
                                datahelper.InsertQuery(row["Time"].ToString(), double.Parse(row[1].ToString()), iclock, imonth);
                            }
                        }
                        catch (Exception)
                        {
                            errornum++;
                        }
                    }
                    //启动函数拟合
                    this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content = "开始" + imonth.ToString() + "月日逐时能耗函数拟合"));
                    Thread thcosthourfitting = new Thread(new ParameterizedThreadStart(costhourfitting));
                    object o = imonth;
                    thcosthourfitting.Start(o);
                }
            }
        }
        public void costhourfitting(Object par)
        {
            int month = (int)par;
            FunctionFitter FF = new FunctionFitter();
         //   FF.CostHourFit(month);//冷拟合
            FF.CostHourFit1(month);//热拟合
            this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content = month.ToString() + "月份逐时能耗关系函数拟合完成"));
        }

        private void btn_LoadDevice_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel|*.xls|Excel|*.xlsx|所有文件|*.*";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;

            if (openFileDialog.ShowDialog().GetValueOrDefault())
            {
                //得到excel中的所有sheet名字，然后循环得到数据模拟表达式
                string workerFileName = openFileDialog.FileName;

                ds600RT = GetSheetbySheetName(workerFileName, "600RT");
                ds1100RT = GetSheetbySheetName(workerFileName, "1100RT");
                dsCondition=GetSheetbySheetName(workerFileName,"空调工况");
                dsIcing = GetSheetbySheetName(workerFileName, "制冰工况");
                lb_status.Content = "开始处理.. 设备数据，请勿关闭窗口";
                lb_fittingstatus.Content = "";
                Thread t600 = new Thread(new ParameterizedThreadStart(device600fitting));
                t600.Start(ds600RT);
                Thread t1100 = new Thread(new ParameterizedThreadStart(device1100fitting));
                t1100.Start(ds1100RT);
                Thread tCondition = new Thread(new ParameterizedThreadStart(deviceConditonfitting));
                tCondition.Start(dsCondition);
                Thread tIcing = new Thread(new ParameterizedThreadStart(IcingDevicefitting));
                tIcing.Start(dsIcing);
            }

        }

        private void device600fitting(object ds)
        {
            DataSet dataS600RT = (DataSet)ds;
            FunctionFitter ff = new FunctionFitter();
            ff.DeviceCostFit(dataS600RT, 1);
            this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content += "600RT机组设备数据能耗关系函数拟合完成\n"));


        }
        private void deviceConditonfitting(object ds)
        {
            DataSet dataSCondition = (DataSet)ds;
            FunctionFitter ff = new FunctionFitter();
            ff.DeviceCostFit(dataSCondition, 3);
            this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content += "冰蓄冷机组空调工况数据能耗关系函数拟合完成\n"));

        }
        private void IcingDevicefitting(object ds)
        {
            DataSet dataSIcing = (DataSet)ds;
            FunctionFitter ff = new FunctionFitter();
            ff.IcingFit(dataSIcing);
            this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content += "冰蓄冷机组制冰工况数据能耗关系函数拟合完成\n"));

        }
        private void device1100fitting(object ds)
        {
            DataSet dataS1100RT = (DataSet)ds;
            FunctionFitter ff = new FunctionFitter();
            ff.DeviceCostFit(dataS1100RT, 2);
            this.Dispatcher.BeginInvoke(new Action(() => lb_fittingstatus.Content += "1100RT机组设备数据能耗关系函数拟合完成\n"));     
        }



    }
}
